<?php

namespace core\database;

/**
 * Database connection layer for MySQL                                          
 *                                                                              
 * This file is part of Scripthulp framework                                    
 *                                                                              
 * @copyright 2012,2013,2014  Rachelle Scheijen                                
 * @author    Rachelle Scheijen                                                
 * @since     1.0                                                              
 * @changed    25/03/12                                                         
 *                                                                              
 * Scripthulp framework is free software: you can redistribute it and/or modify 
 * it under the terms of the GNU Lesser General Public License as published by  
 * the Free Software Foundation, either version 3 of the License, or            
 * (at your option) any later version.                                          
 *                                                                              
 * Scripthulp framework is distributed in the hope that it will be useful,      
 * but WITHOUT ANY WARRANTY; without even the implied warranty of               
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the                
 * GNU General Public License for more details.                                 
 *                                                                              
 * You should have received a copy of the GNU Lesser General Public License     
 * along with Scripthulp framework.  If not, see <http://www.gnu.org/licenses/>.
 */
class Database_Mysqli implements DAL{

  private $service_Settings;
  private $obj_connection;
  private $obj_query;
  private $bo_connection;
  private $s_lastDatabase;
  private $i_id;
  private $i_affected_rows;
  private $bo_transaction = false;
  private $bo_binded = false;
  private $obj_binded;

  /**
   * Loads the binded parameters class
   * 
   * @param \core\services\Settings $service_Settings   The settings service
   */
  public function __construct(\core\services\Settings $service_Settings){
    require_once(NIV . 'include/database/mysqli_binded.inc.php');
    $this->obj_binded = new Database_Mysqli_binded($this);
    $this->service_Settings = $service_Settings;
  }

  /**
   * Destructor
   */
  public function __destruct(){
    if( $this->bo_connection ){
      $this->connectionEnd();
    }

    $this->service_Settings = null;
    $this->obj_connection = null;
    $this->obj_query = null;
    $this->bo_connection = null;
    $this->s_lastDatabase = null;
    $this->i_id = null;
    $this->i_affected_rows = null;
    $this->bo_transaction = null;
  }

  /**
   * Connects to the database with the preset login data 
   */
  public function defaultConnect(){
    $this->bo_connection = false;

    $s_type = $this->service_Settings->get('settings/SQL/type');
    $this->connection($this->service_Settings->get('settings/SQL/' . $s_type . '/username'), $this->service_Settings->get('settings/SQL/' . $s_type . '/password'), $this->service_Settings->get('settings/SQL/' . $s_type . '/database'), $this->service_Settings->get('settings/SQL/' . $s_type . '/host'), $this->service_Settings->get('settings/SQL/' . $s_type . '/port'));

    $this->reset();
  }

  /**
   * Resets the internal query cache.
   */
  public function reset(){
    if( is_object($this->obj_query) && !$this->bo_transaction ){
      if( $this->bo_binded ){
        $this->obj_binded->clearResult();
        $this->bo_binded = false;
      }
      else {
        $this->obj_query->free_result();
      }
    }

    $this->obj_query = null;
    $this->i_id = -1;
    $this->i_affected_rows = -1;
  }

  /**
   * Returns numbers of rows affected generated by a UPDATE or DELETE command
   *
   * @return  int The requested id
   */
  public function affected_rows(){
    return $this->i_affected_rows;
  }

  /**
   * Checks if the given connection-data is correct
   *
   * @static
   * @param   string  $s_username     The username
   * @param   string  $s_password     The password
   * @param   string  $s_database     The database
   * @param   string  $s_host         The host name, default 127.0.0.1 (localhost)
   * @param   int     $i_port         The port
   * @return  boolean True if the data is correct, otherwise false
   */
  public static function checkLogin($s_username, $s_password, $s_database, $s_host = '127.0.0.1', $i_port = -1){
    if( $i_port == -1 ) $i_port = '';

    if( empty($s_username) || empty($s_host) || empty($s_database) ) return false;

    /* Check for current settings */
    if( file_exists(NIV . 'admin/data/settings/settings.xml') && class_exists('\core\Memory') && !is_null($this->service_Settings) ){
      $s_type = $this->service_Settings->get('settings/SQL/type');

      if( $s_username == $this->service_Settings->get('settings/SQL/' . $s_type . '/username') && $s_password == $this->service_Settings->get('settings/SQL/' . $s_type . '/password') && $s_database == $this->service_Settings->get('settings/SQL/' . $s_type . '/database') && $s_host == $this->service_Settings->get('settings/SQL/' . $s_type . '/host') && $i_port == $this->service_Settings->get('settings/SQL/' . $s_type . '/port') ){
        return true;
      }
    }

    /* connect to the database */
    if( $i_port == -1 || $i_port == '' ){
      $obj_connection = new \mysqli($s_host, $s_username, $s_password, $s_database);
    }
    else {
      $obj_connection = new \mysqli($s_host, $s_username, $s_password, $s_database, $i_port);
    }
    if( $obj_connection->connect_errno ){
      return false;
    }

    $obj_connection->close();
    unset($obj_connection);

    return true;
  }

  /**
   * Connects to the set database
   * 
   * @param   string  $s_username     The username
   * @param   string  $s_password     The password
   * @param   string  $s_database     The database
   * @param   string  $s_host         The host name, default 127.0.0.1 (localhost)
   * @param   int     $i_port         The port
   * @throws  DBException     If connection to the database failed
   */
  public function connection($s_username, $s_password, $s_database, $s_host = '127.0.0.1', $i_port = -1){
    if( $this->bo_connection ) return;

    $this->bo_connection = false;

    /* connect to the database */
    if( $i_port == -1 || $i_port == '' ){
      $this->obj_connection = new \mysqli($s_host, $s_username, $s_password, $s_database);
    }
    else {
      $this->obj_connection = new \mysqli($s_host, $s_username, $s_password, $s_database, $i_port);
    }
    if( $this->obj_connection->connect_errno ){
      /* Error connecting */
      throw new \DBException("Error connection to database " . $s_database . '. Check the connection-settings');
    }

    $this->s_lastDatabase = $s_database;
    $this->bo_connection = true;
  }

  /**
   * Closes the connection to the mysql database
   */
  public function connectionEnd(){
    if( $this->bo_connection ){
      $this->obj_connection->close();
      $this->bo_connection = false;
    }
  }

  /**
   * Escapes the given data for save use in queries
   *
   * @param   string  $s_data  The data that need to be escaped
   * @return  string  The escaped data
   */
  public function escape_string($s_data){
    $s_data = htmlentities($s_data, ENT_QUOTES);

    return $this->obj_connection->real_escape_string($s_data);
  }

  /**
   * Returns the results of the query in a numeric array
   *
   * @return  array	The data-set
   * @throws  DBException when no SELECT-query was excequeted
   */
  public function fetch_row(){
    $this->checkSelect();

    if( $this->bo_binded ){
      return $this->obj_binded->fetch_row();
    }

    $a_temp = array();
    while( $a_res = $this->obj_query->fetch_row() ){
      $a_temp[] = $a_res;
    }

    return $a_temp;
  }

  /**
   * Returns the results of the query in a associate and numeric array
   *
   * @return  array	The data-set
   * @throws  DBException when no SELECT-query was excequeted
   */
  public function fetch_array(){
    $this->checkSelect();

    if( $this->bo_binded ){
      return $this->obj_binded->fetch_array();
    }

    $a_temp = array();
    while( $a_res = $this->obj_query->fetch_array() ){
      $a_temp[] = $a_res;
    }

    return $a_temp;
  }

  /**
   * Returns the results of the query in a associate array
   *
   * @return          array	The data-set
   * @throws          DBException when no SELECT-query was excequeted
   */
  public function fetch_assoc(){
    $this->checkSelect();

    if( $this->bo_binded ){
      return $this->obj_binded->fetch_assoc();
    }

    $a_temp = array();
    while( $a_res = $this->obj_query->fetch_assoc() ){
      $a_temp[] = $a_res;
    }

    return $a_temp;
  }

  /**
   * Returns the results of the query in a associate array with the given field as counter-key
   *
   * @param          string  The field that is the counter-key
   * @return			array	The data-set sorted on the given key
   * @throws         DBException when no SELECT-query was excequeted
   */
  public function fetch_assoc_key($s_key){
    $this->checkSelect();

    if( $this->bo_binded ){
      return $this->obj_binded->fetch_assoc_key($s_key);
    }

    $a_temp = array();
    while( $a_res = $this->obj_query->fetch_assoc() ){
      $a_temp[ $a_res[ $s_key ] ] = $a_res;
    }

    return $a_temp;
  }

  /**
   * Returns the results of the query as a object-array
   *
   * @return          object	The data-set
   * @throws          Exception when no SELECT-query was excequeted
   */
  public function fetch_object(){
    $this->checkSelect();

    if( $this->bo_binded ){
      return $this->obj_binded->fetch_object();
    }

    $a_temp = array();
    while( $obj_res = $this->obj_query->fetch_object() ){
      $a_temp[] = $a_obj;
    }

    return $a_temp;
  }

  /**
   * Returns the result from the query with the given row and field
   *
   * @param           int     The row
   * @param           string  The field
   * @return          string  The content of the requested result-field
   * @throws          DBException if no SELECT-query was excequeted
   */
  public function result($i_row, $s_field){
    $this->checkSelect();

    if( $i_row > $this->num_rows() || $i_row < 0 ) throw new \DBException("Trying to get data from a not existing field");

    if( $this->bo_binded ){
      return $this->obj_binded->result($i_row, $s_field);
    }

    $this->obj_query->data_seek($i_row);
    $a_data = $this->obj_query->fetch_assoc();
    $this->obj_query->data_seek(0);

    if( !array_key_exists($s_field, $a_data) ){
      throw new \DBException("Trying to get data from a not existing field");
    }
    return $a_data[ $s_field ];
  }

  /**
   * Returns the ID generated by a INSERT-command
   *
   * @return	int The generated id
   */
  public function getId(){
    return $this->i_id;
  }

  /**
   * Returns or there is a connection to the database
   *
   * @return	boolean True if there is a connection with the DB, false if is not
   */
  public function isConnected(){
    return $this->bo_connection;
  }

  /**
   * Returns the number of results from the last excequeted query
   *
   * @return          int The number of results
   * @throws          DBException when no SELECT-query was excequeted
   */
  public function num_rows(){
    if( is_null($this->obj_query) ){
      throw new \DBException("Trying to count the numbers of results on a non-SELECT-query");
    }

    return $this->obj_query->num_rows;
  }

  /**
   * Excequetes the given query on the selected database with binded parameters
   *
   * @param	 string	$s_query	The query to excequte
   * @param	 array	$a_types	The value types : i (int) ,d (double) ,s (string) or b (blob)
   * @param	 array  $a_values	The values
   * @throws  Exception if the arguments are illegal
   * @throws  DBException when the query failes
   */
  public function queryBinded($s_query, $a_types, $a_values){
    if( is_null($s_query) || empty($s_query) ){
      throw new \Exception("Illegal query call " . $s_query);
    }

    $this->reset();

    if( is_null($this->obj_connection) ) throw new \DBException("No connection to the database");

    $this->obj_binded->queryBindedProcess($s_query, $a_types, $a_values, $this->obj_connection);

    $this->bo_binded = true;
    $this->i_id = $this->obj_binded->i_id;
    $this->i_affected_rows = $this->obj_binded->i_affected_rows;
    $this->obj_query = $this->obj_binded->obj_query;
  }

  /**
   * Excequetes the given query on the selected database
   *
   * @para            string  $s_query    The query to excequte
   * @throws         DBException if no connection to the database exists
   * @throws         DBException in case of a SQL error
   */
  public function query($s_query){
    if( !$this->bo_connection ) throw new \DBException("No connection to the database");

    $this->reset();

    $this->bo_binded = false;
    $qry_sql = $this->obj_connection->query($s_query);

    if( !$qry_sql ){
      throw new \DBException("Query failed : " . $this->obj_connection->error . '\n' . $s_query);
    }

    $s_command = strtoupper(trim(substr($s_query, 0, strpos($s_query, ' '))));
    if( $s_command == 'SELECT' || $s_command == 'SHOW' || $s_command == 'ANALYZE' ){
      $this->obj_query = $qry_sql;
    }
    if( $s_command == 'INSERT' ){
      $this->i_id = $this->obj_connection->insert_id;
    }
    else if( $s_command == 'UPDATE' || $s_command == 'DELETE' ){
      $this->i_affected_rows = $this->obj_connection->affected_rows;
    }
  }

  /**
   * Starts a new transaction
   * 
   * @throws DBException	If a transaction is allready active
   */
  public function transaction(){
    if( $this->bo_transaction ){
      throw new \DBException("Can not start new transaction. Call commit() or rollback() first.");
    }

    $this->query("START TRANSACTION");
    $this->bo_transaction = true;
  }

  /**
   * Commits the current transaction
   * 
   * @throws DBException	If no transaction is active
   */
  public function commit(){
    if( !$this->bo_transaction ){
      throw new \DBException("Can not commit transaction. Call transaction() first.");
    }

    $this->query("COMMIT");
    $this->bo_transaction = false;
  }

  /**
   * Rolls the current transaction back
   * 
   * @throws DBException	If no transaction is active
   */
  public function rollback(){
    if( !$this->bo_transaction ){
      throw new \DBException("Can not rollback transaction. Call transaction() first.");
    }

    $this->query("ROLLBACK");
    $this->bo_transaction = false;
  }

  /**
   * Analyses the given table
   *
   * @param string $s_table		The table name
   * @return boolean	True if the table is OK, otherwise false
   */
  public function analyse($s_table){
    $this->query("ANALYZE TABLE " . $s_table);

    $a_result = $this->fetch_assoc();
    if( $a_result[ 0 ][ 'Msg_text' ] != 'OK' && $a_result[ 0 ][ 'Msg_text' ] != 'Table is already up to date' ){
      return false;
    }

    return true;
  }

  /**
   * Repairs the given table
   *
   * @param string $s_table		The table name
   * @return boolean	True if the table repair succeeded, otherwise false
   */
  public function repair($s_table){
    $this->query("REPAIR TABLE " . $s_table);

    $a_result = $this->fetch_assoc();
    if( $a_result[ 0 ][ 'Msg_text' ] != 'OK' ){
      return false;
    }

    return true;
  }

  /**
   * Optimizes the given table
   *
   * @param string $s_table		The table name
   */
  public function optimize($s_table){
    $this->query("OPTIMIZE TABLE " . $s_table);

    $a_result = $this->fetch_assoc();
    if( $a_result[ 0 ][ 'Msg_text' ] != 'OK' && $a_result[ 0 ][ 'Msg_text' ] != 'Table is already up to date' ){
      return false;
    }

    return true;
  }

  /**
   * Changes the active database to the given one
   *
   * @param	string	$s_database	The new database
   * @throws	DBException if the new databases does not exist or no access
   */
  public function useDB($s_database){
    try{
      $this->query("USE " . $s_database);
    }
    catch( \Exception $ex ){
      throw new \Exception("Database-change failed .\n" . $s_database);
    }
  }

  /**
   * Checks if a database exists and if the user has access to it
   *
   * @param	string	$s_database	The database
   * @return  boolean	True if the database exists, otherwise false
   */
  public function databaseExists($s_database){
    $this->query("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '" . $s_database . "'");
    if( $this->num_rows() > 0 ) return true;

    return false;
  }

  /**
   * Checks if the last query was a SELECT-query
   *
   * @throws  DBException when no SELECT-query was excequeted
   */
  private function checkSelect(){
    if( $this->obj_query == null ){
      throw new \DBException("Database-error : trying to get data on a non-SELECT-query");
    }
  }

}
?>
